const express = require("express");
const db = require("../../utils/db");
const router = express.Router();

// 待收费数据渲染与查询
router.post("/chargeMGTData", (req, res) => {
  const { a_type, s_name, s_createTime, u_name, paginalNumber, page, a_id } =
    req.body;
  let sql = `SELECT * FROM expense_record  
  JOIN doctor ON expense_record.a_u_id = doctor.u_id 
  JOIN medicinal_information ON expense_record.a_m_id = medicinal_information.m_id
  JOIN register_record ON expense_record.a_reg_id = register_record.reg_id
  JOIN  patient ON expense_record.a_s_id = patient.s_id
  WHERE a_status = 0 AND a_type LIKE '%${a_type}%' AND s_name LIKE '%${s_name}%' AND s_createTime LIKE '%${s_createTime}%' 
  AND u_name LIKE '%${u_name}%' LIMIT ${
    (page - 1) * paginalNumber
  },${paginalNumber};`;
  let sql2 = `SELEcT COUNT(*) AS total FROM expense_record  
  JOIN doctor ON expense_record.a_u_id = doctor.u_id 
  JOIN  patient ON expense_record.a_s_id = patient.s_id
  WHERE a_status = 0 and a_type LIKE '%${a_type}%' AND s_createTime LIKE '%${s_createTime}%' 
  AND u_name LIKE '%${u_name}%';`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data: data, msg: "success", total: data2[0].total });
        }
      });
    }
  });
});

// 已收费数据渲染与查询
router.post("/chargePriceMGTData", (req, res) => {
  const { a_type, s_name, s_createTime, u_name, paginalNumber, page } =
    req.body;
  let sql = `SELECT * FROM expense_record  
  JOIN doctor ON expense_record.a_u_id = doctor.u_id 
  JOIN medicinal_information ON expense_record.a_m_id = medicinal_information.m_id
  JOIN  patient ON expense_record.a_s_id = patient.s_id
  JOIN register_record ON expense_record.a_reg_id = register_record.reg_id
  WHERE a_status = 1 AND a_type LIKE '%${a_type}%' AND s_name LIKE '%${s_name}%' AND s_createTime LIKE '%${s_createTime}%' 
  AND u_name LIKE '%${u_name}%' LIMIT ${
    (page - 1) * paginalNumber
  },${paginalNumber};`;
  let sql2 = `SELECT COUNT(*) AS total FROM expense_record  
  JOIN doctor ON expense_record.a_u_id = doctor.u_id 
  JOIN  patient ON expense_record.a_s_id = patient.s_id
  WHERE a_status = 1 and a_type LIKE '%${a_type}%' AND s_createTime LIKE '%${s_createTime}%' 
  AND u_name LIKE '%${u_name}%';`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data: data, msg: "success", total: data2[0].total });
        }
      });
    }
  });
});

// 已退款数据渲染与查询
router.post("/chargeRefundMGTData", (req, res) => {
  const { a_type, s_name, s_createTime, u_name, paginalNumber, page } =
    req.body;
  let sql = `SELECT * FROM expense_record  
  JOIN doctor ON expense_record.a_u_id = doctor.u_id 
  JOIN medicinal_information ON expense_record.a_m_id = medicinal_information.m_id
  JOIN register_record ON expense_record.a_reg_id = register_record.reg_id
  JOIN  patient ON expense_record.a_s_id = patient.s_id
  WHERE a_status = 2 AND a_type LIKE '%${a_type}%' AND s_name LIKE '%${s_name}%' AND s_createTime LIKE '%${s_createTime}%' 
  AND u_name LIKE '%${u_name}%' LIMIT ${
    (page - 1) * paginalNumber
  },${paginalNumber};`;
  let sql2 = `SELECT COUNT(*) AS total FROM expense_record  
  JOIN doctor ON expense_record.a_u_id = doctor.u_id 
  JOIN  patient ON expense_record.a_s_id = patient.s_id
  WHERE a_status = 2 and a_type LIKE '%${a_type}%' AND s_createTime LIKE '%${s_createTime}%' 
  AND u_name LIKE '%${u_name}%';`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data: data, msg: "success", total: data2[0].total });
        }
      });
    }
  });
});

// 下拉框
router.post("/select", (req, res) => {
  let sql = `  SELECT u_name FROM doctor`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 删除
router.post("/del", (req, res) => {
  const { a_id } = req.body;
  let sql = `DELETE FROM expense_record WHERE a_id = ${a_id};`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 修改状态
router.post("/updateStatus", (req, res) => {
  const { a_id } = req.body;
  let sql = `UPDATE expense_record SET a_status=2 WHERE a_id=${a_id};`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
// 收费-修改状态
router.post("/updateRefundStatus", (req, res) => {
  const { a_id } = req.body;
  let sql = `UPDATE expense_record SET a_status=1 WHERE a_id=${a_id};`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
// 零售药品排行
router.post("/drugSales", (req, res) => {
  const { m_name, m_createTime, m_type, paginalNumber, page } = req.body;
  let sql = `SELECT * FROM medicinal_information WHERE m_name LIKE'%${m_name}%' 
  AND  m_createTime LIKE '%${m_createTime}%' AND m_type LIKE '%${m_type}%'  LIMIT ${
    (page - 1) * paginalNumber
  },${paginalNumber};`;
  let sql2 = `SELECT COUNT(*) AS total FROM medicinal_information WHERE m_name LIKE'%${m_name}%' 
  AND  m_createTime LIKE '%${m_createTime}%' AND m_type LIKE '%${m_type}%'`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data: data, msg: "success", total: data2[0].total });
        }
      });
    }
  });
});

// 零售药品排行下拉框
router.post("/drugSalesSelect", (req, res) => {
  let sql = ` SELECT DISTINCT m_type FROM medicinal_information`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 性别统计
router.post("/sexCount", (req, res) => {
  let sql = `SELECT (SELECT COUNT(1) AS num FROM patient WHERE s_sex=0) AS male, 
  (SELECT COUNT(1) AS num FROM patient WHERE s_sex=1) AS female,
  (SELECT COUNT(*) AS num FROM patient) AS total`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 收支明细
router.post("/detail", (req, res) => {
  const { a_type, s_name, s_createTime, u_name, paginalNumber, page } =
    req.body;
  // 数据
  let sql = `SELECT * FROM expense_record  
  JOIN doctor ON expense_record.a_u_id = doctor.u_id 
  JOIN medicinal_information ON expense_record.a_m_id = medicinal_information.m_id
  JOIN register_record ON expense_record.a_reg_id = register_record.reg_id
  JOIN  patient ON expense_record.a_s_id = patient.s_id
  WHERE a_type LIKE '%${a_type}%' AND s_name LIKE '%${s_name}%' AND s_createTime LIKE '%${s_createTime}%' 
  AND u_name LIKE '%${u_name}%' LIMIT ${
    (page - 1) * paginalNumber
  },${paginalNumber};`;
  // 总数据数
  let sql2 = `SELECT COUNT(*) AS total FROM expense_record  
  JOIN doctor ON expense_record.a_u_id = doctor.u_id 
  JOIN  patient ON expense_record.a_s_id = patient.s_id
  WHERE a_type LIKE '%${a_type}%' AND s_createTime LIKE '%${s_createTime}%' 
  AND u_name LIKE '%${u_name}%';`;
  // 微信
  let sql3 = `SELECT SUM(m_price*m_count+47 *(SELECT COUNT(*) FROM expense_record WHERE a_payMethods=0)) AS Wechat FROM medicinal_information  
  JOIN expense_record ON medicinal_information.m_id = expense_record.a_m_id 
  WHERE a_payMethods=0`;
  // 支付宝
  let sql4 = `SELECT SUM(m_price*m_count+47 *(SELECT COUNT(*) FROM expense_record WHERE a_payMethods=1)) AS alipay FROM medicinal_information  
  JOIN expense_record ON medicinal_information.m_id = expense_record.a_m_id 
  WHERE a_payMethods=1`;
  // 现金
  let sql5 = `SELECT SUM(m_price*m_count+47 *(SELECT COUNT(*) FROM expense_record WHERE a_payMethods=2)) AS cash FROM medicinal_information  
  JOIN expense_record ON medicinal_information.m_id = expense_record.a_m_id 
  WHERE a_payMethods=2`;
  // 总金额
  let sql6 = `SELECT SUM(m_price*m_count+47 *(SELECT COUNT(*) FROM expense_record )) AS totalPrice FROM medicinal_information `;
  // 折后金额
  let sql7 = `SELECT SUM(m_priceDiscount*m_count+47 *(SELECT COUNT(*) FROM expense_record )) AS receivable FROM medicinal_information `;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err, msg: "error" });
        } else {
          db.query(sql3, (err3, data3) => {
            if (err3) {
              res.send({ err, msg: "error" });
            } else {
              db.query(sql4, (err4, data4) => {
                if (err4) {
                  res.send({ err, msg: "error" });
                } else {
                  db.query(sql5, (err5, data5) => {
                    if (err5) {
                      res.send({ err, msg: "error" });
                    } else {
                      db.query(sql6, (err6, data6) => {
                        if (err6) {
                          res.send({ err, msg: "error" });
                        } else {
                          db.query(sql7, (err7, data7) => {
                            if (err7) {
                              res.send({ err, msg: "error" });
                            } else {
                              res.send({
                                data: data,
                                msg: "success",
                                total: data2[0].total,
                                Wechat: data3[0].Wechat,
                                alipay: data4[0].alipay,
                                cash: data5[0].cash,
                                totalPrice: data6[0].totalPrice,
                                receivable: data7[0].receivable,
                              });
                            }
                          });
                        }
                      });
                    }
                  });
                }
              });
            }
          });
        }
      });
    }
  });
});

// 附加费设置
router.post("/additionSettings", (req, res) => {
  const { p_person, paginalNumber, page } = req.body;
  let sql = `SELECT * FROM price_setting
  WHERE p_person LIKE '%${p_person}%' LIMIT ${
    (page - 1) * paginalNumber
  },${paginalNumber};`;
  let sql2 = `SELECT COUNT(*) AS total FROM price_setting
  WHERE p_person  LIKE '%${p_person}%'`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data: data, msg: "success", total: data2[0].total });
        }
      });
    }
  });
});

//  附加费删除
router.post("/addDel", (req, res) => {
  const { p_id } = req.body;
  let sql = `DELETE FROM price_setting WHERE p_id=${p_id};`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 编辑附加费
router.post("/editAdd", (req, res) => {
  const { p_id, p_name, p_recipelType, p_price, p_status } = req.body;
  let sql = `UPDATE price_setting SET p_name='${p_name}', p_recipelType = ${p_recipelType},p_price = ${p_price},p_status = ${p_status} WHERE p_id=${p_id};`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 新增附加费
router.post("/Add", (req, res) => {
  const { p_recipelType, p_name, p_price, p_status, p_createTime, p_person } =
    req.body;
  let sql = `INSERT INTO price_setting VALUES(NULL,DEFAULT,${p_recipelType},${p_price},'2022-12-15',${p_status},'${p_name}','小韩')`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 挂号费设置
router.post("/register", (req, res) => {
  const { u_name, paginalNumber, page } = req.body;
  let sql = `SELECT * FROM register_record JOIN doctor ON register_record.reg_user_id = doctor.u_id
  WHERE u_name LIKE '%${u_name}%'  LIMIT ${
    (page - 1) * paginalNumber
  },${paginalNumber};`;
  let sql2 = `SELECT COUNT(*) AS total FROM register_record JOIN doctor ON register_record.reg_user_id = doctor.u_id
  WHERE u_name LIKE '%${u_name}%'`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data: data, msg: "success", total: data2[0].total });
        }
      });
    }
  });
});

//  挂号费删除
router.post("/registerDel", (req, res) => {
  const { reg_id } = req.body;
  let sql = `DELETE FROM register_record WHERE reg_id=${reg_id};`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 挂号费编辑
router.post("/registerEdit", (req, res) => {
  const { reg_id, reg_name, reg_priceType,reg_price } = req.body;
  let sql = `UPDATE register_record SET reg_name='${reg_name}', reg_price = ${reg_price},reg_priceType = ${reg_priceType} WHERE reg_id=${reg_id};`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 新增挂号费
router.post("/registerAdd", (req, res) => {
  const { reg_price, reg_name, reg_priceType } = req.body;
  let sql = `SET FOREIGN_KEY_CHECKS = 0;`;
  let sql2 = `INSERT INTO register_record VALUES(NULL,10024,'2022-12-16',${reg_price},DEFAULT,DEFAULT,10024,DEFAULT,${reg_name},${reg_priceType});`;
  let sql3 = `SET FOREIGN_KEY_CHECKS = 1;`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          db.query(sql3, (err3, data3) =>{
            if(err3) {
              res.send({ err3: err, msg: "error" });
            }else{
              res.send({ data: data2, msg: "success" });
            }
          })
        }
      });
    }
  });
});

// 门诊日志
router.post("/OutpatientLog", (req, res) => {
  const { s_name, reg_time, u_name, paginalNumber, page } = req.body;
  let sql = ` SELECT * FROM register_record JOIN doctor ON register_record.reg_user_id = doctor.u_id 
  JOIN patient ON register_record.reg_s_id = patient.s_id 
  WHERE u_name LIKE '%${u_name}%' AND reg_time LIKE '%${reg_time}%' AND s_name LIKE '%${s_name}%'
  LIMIT ${(page - 1) * paginalNumber},${paginalNumber};`;
  let sql2 = `SELEcT COUNT(*) AS total FROM register_record JOIN doctor ON register_record.reg_user_id = doctor.u_id 
  JOIN patient ON register_record.reg_s_id = patient.s_id 
  WHERE u_name LIKE '%${u_name}%' AND reg_time LIKE '%${reg_time}%' AND s_name LIKE '%${s_name}%'`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          res.send({ data: data, msg: "success", total: data2[0].total });
        }
      });
    }
  });
});


// 年龄统计
router.post("/ageCount", (req, res) => {
  let sql = ` SELECT s_age,
  SUM(CASE WHEN s_age <=10 THEN 1 ELSE 0 END) AS 'age1',
  SUM(CASE WHEN s_age BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS 'age2',
  SUM(CASE WHEN s_age BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS 'age3',
  SUM(CASE WHEN s_age BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS 'age4',
  SUM(CASE WHEN s_age BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS 'age5',
  SUM(CASE WHEN s_age BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS 'age6',
  SUM(CASE WHEN s_age BETWEEN 61 AND 70 THEN 1 ELSE 0 END) AS 'age7',
  SUM(CASE WHEN s_age BETWEEN 71 AND 80 THEN 1 ELSE 0 END) AS 'age8',
  SUM(CASE WHEN s_age >=81 THEN 1 ELSE 0 END) AS 'age9' 
  FROM patient`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
// 根据订单编号查询
router.post("/registerSearch", (req, res) => {
  const { a_id } = req.body;
  let sql = `SELECT * FROM expense_record  
  JOIN doctor ON expense_record.a_u_id = doctor.u_id 
  JOIN medicinal_information ON expense_record.a_m_id = medicinal_information.m_id
  JOIN register_record ON expense_record.a_reg_id = register_record.reg_id
  JOIN  patient ON expense_record.a_s_id = patient.s_id
  WHERE expense_record.a_id=${a_id}`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
module.exports = router;
